package com.jiexin.schedule;

import com.jiexin.dao.cat.CATMapper;
import com.jiexin.dao.dsh.DSHMapper;
import com.jiexin.util.ConfigUtil;
import com.jiexin.util.ExceptionUtil;
import org.dom4j.*;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.xml.bind.DatatypeConverter;
import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @Author: Jay_Liu
 * @Description: 将axml文件解析成基础表的数据(item 、 site 、 bom 、 affected item 、 change)
 * @Trigger：
 * @Date: Created in 10:55 2018/8/22
 * @Modified By:
 */

@SuppressWarnings({"rawtypes", "unchecked"})
@Component("extractHandler")
public class ExtractHandler {

    private static final Logger LOG = LoggerFactory.getLogger(ExtractHandler.class);

    private Map metaCache = new HashMap();

    private Map<String, XPath> xpathCache = new ConcurrentHashMap<String, XPath>();

    @Resource
    private DSHMapper dshMapper;

    @Resource
    private CATMapper catMapper;

    @Resource(name = "dshDataSourceJdbcTemplate")
    private JdbcTemplate dshDataSourceJdbcTemplate;

    private boolean quoteTableName = Boolean.parseBoolean(ConfigUtil.getQuoteTableName());

    private String NS = ConfigUtil.getNamespace();

    private String xpathFilterRootNodes = ConfigUtil.getRootNodes();

    private String xpathFilterAI = ConfigUtil.getAffectItem();

    private String xpathFilterSites = ConfigUtil.getSites();

    private String xpathFilterBOM = ConfigUtil.getBom();

    private List<XMLLoader> loaders = new ArrayList<XMLLoader>();

    private XMLLoader FALLBACK = new FallbackXMLLoader();

    /**
     * init mapping data
     */
    @PostConstruct
    public void initMetaCache() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM META_XML_DB");

        this.dshDataSourceJdbcTemplate.query(sql.toString(), new RowCallbackHandlerImpl(metaCache));

        LOG.trace("metaCache: {}", metaCache);

        loaders.add(new ItemXMLLoader());
        loaders.add(new ChangeXMLLoader());
    }

    /**
     * fetch mapping data according to the specify classification
     *
     * @param classTag
     * @param tableTag
     * @param fieldTag
     * @return mapping fields
     */
    public String[] getFieldMetaInfo(String classTag, String tableTag, String fieldTag) {
        Map cfgByClass = (Map) this.metaCache.get(classTag);
        if (cfgByClass == null) {
            return null;
        }
        Map cfgByTable = (Map) cfgByClass.get(tableTag);
        if (cfgByTable == null) {
            return null;
        }
        String[] metaByField = (String[]) cfgByTable.get(fieldTag);
        return metaByField;
    }

    /**
     * parse current axml file
     *
     * @param tmp
     * @param pid
     * @param atoNumber
     * @param currentFileName
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void persist(File tmp, long pid, String atoNumber, String currentFileName) throws Exception {

        try {
            doJDBC(tmp, pid, atoNumber, currentFileName);
        } catch (Exception e) {
            LOG.error("When executing doJDBC()", e);
            throw e;
        } finally {
            delete(tmp);
        }
    }

    /**
     * delete file
     *
     * @param file
     */
    public void delete(File file) {

        if (file == null || !file.exists()) {
            return;
        }
        if (!file.delete()) {
            file.deleteOnExit();
        }
    }

    /**
     * build name spaces
     *
     * @param ns
     * @return name spaces
     */
    public Map<String, String> buildNamespace(String ns) {

        Map<String, String> result = new HashMap<String, String>();
        String[] namespaces = ns.split("\\s*,\\s*");
        for (String namespace : namespaces) {
            String[] kv = namespace.split("=");
            if (kv.length == 2) {
                result.put(kv[0], kv[1]);
            } else {
                result.put("axml", kv[0]);
            }
        }

        return result;
    }

    /**
     * select the specify node to fetch data
     *
     * @param ctxNode
     * @param xpath
     * @param namespaces
     * @return nodes
     */
    public List selectNodes(Node ctxNode, String xpath, Map<String, String> namespaces) {

        XPath xpathObj = xpathCache.get(xpath);
        if (xpathObj == null) {
            xpathObj = DocumentHelper.createXPath(xpath);
            xpathObj.setNamespaceURIs(namespaces);
            xpathCache.put(xpath, xpathObj);
        }
        List objNodes = xpathObj.selectNodes(ctxNode);
        return objNodes;
    }

    /**
     * fetch the single node according to node object
     *
     * @param ctxNode
     * @param xpath
     * @param namespaces
     * @return node
     */
    public Node selectSingleNode(Node ctxNode, String xpath, Map<String, String> namespaces) {

        List nodes = selectNodes(ctxNode, xpath, namespaces);
        if (nodes != null && nodes.size() > 0) {
            return (Node) nodes.get(0);
        } else {
            return null;
        }
    }

    /**
     * fetch the value of node
     *
     * @param ctxNode
     * @param xpath
     * @param namespaces
     * @return value
     */
    public String selectSingleNodeText(Node ctxNode, String xpath, Map<String, String> namespaces) {

        Node node = selectSingleNode(ctxNode, xpath, namespaces);
        return node != null ? node.getText().trim() : null;
    }

    /**
     * parse axml to basic table
     *
     * @param xmlFile
     * @param pid
     * @param atoNumber
     * @param currentFileName
     * @throws Exception
     */
    public void doJDBC(File xmlFile, long pid, String atoNumber, String currentFileName) throws Exception {

        LOG.debug("xmlFile: {}", xmlFile.getAbsolutePath());
        Map<String, String> namespaces = buildNamespace(NS);
        LOG.debug("namespaces: {}", namespaces);
        SAXReader reader = new SAXReader();
        Document document = reader.read(xmlFile);
        List objNodes = selectNodes(document, xpathFilterRootNodes, namespaces);
        if (objNodes != null) {
            List<String> errors = new ArrayList<String>();
            List<String> finished = new ArrayList<String>();
            for (int i = 0; i < objNodes.size(); i++) {
                Node objNode = (Node) objNodes.get(i);
                try {
                    XMLLoader loader = getXMLLoader(objNode);
                    loader.doJDBC(namespaces, document, (Element) objNode, finished, pid, atoNumber, currentFileName);
                } catch (Exception e) {
                    LOG.error("When executing doJDBC()", e);
                    String msg = ExceptionUtil.createError(e);
                    errors.add(msg);
                }
            }
            if (errors.size() > 0) {
                StringBuilder msg = new StringBuilder();
                for (String error : errors) {
                    if (msg.length() > 0) {
                        msg.append("\n");
                    }
                    msg.append(error);
                }
                throw new Exception(msg.toString());
            }

        }
    }


    public XMLLoader getXMLLoader(Node node) {

        for (XMLLoader loader : loaders) {
            if (loader.supports(node.getName())) {
                return loader;
            }
        }
        return FALLBACK;
    }


    public abstract class XMLLoader {

        public abstract boolean supports(String nodeName);

        public abstract String getXPathForNumber();

        public boolean isTitleBlock(String tableName) {
            if ("PageTwo".equalsIgnoreCase(tableName)
                    || "PageThree".equalsIgnoreCase(tableName)) {
                return true;
            }
            return false;
        }

        /**
         * bulid SQL
         *
         * @param classTag
         * @param tableNode
         * @param sql
         * @param cols
         * @param placeHolders
         * @param args
         * @param hasTableInSql
         * @return flag
         */
        public boolean buildSQL(String classTag, Element tableNode, StringBuilder sql,
                                StringBuilder cols, StringBuilder placeHolders, List args, boolean hasTableInSql) {
            LOG.trace("In buildSQL(), classTag: {}", classTag);

            String tableTag = tableNode.getName();
            LOG.trace("In buildSQL(), tableTag: {}", tableTag);

            List fields = tableNode.elements();

            String dbTable = null;
            for (int j = 0; j < fields.size(); j++) {
                Element field = (Element) fields.get(j);
                String fieldTag = field.getName();
                LOG.trace("In buildSQL(), fieldTag: {}", fieldTag);
                String[] dbInfo = getFieldMetaInfo(classTag, tableTag, fieldTag);
                if (dbInfo != null) {
                    if (dbTable == null) {
                        dbTable = dbInfo[0];
                    }
                    if (cols.length() > 0) {
                        cols.append(",");
                        placeHolders.append(",");
                    }
                    cols.append(dbInfo[1]);
                    placeHolders.append("?");

                    if (field.isTextOnly()) {
                        if (dbInfo[1].indexOf("DATE") > 0) {
                            String value = field.getTextTrim();
                            Calendar date = DatatypeConverter.parseDateTime(value);
                            args.add(date.getTime());
                        } else {
                            args.add(field.getTextTrim());
                        }
                    } else {
                        StringBuilder vals = new StringBuilder();
                        for (Iterator multiList = field.elementIterator(); multiList.hasNext(); ) {
                            if (vals.length() > 0) {
                                vals.append(";");
                            }
                            Element mlVal = (Element) multiList.next();
                            vals.append(mlVal.getTextTrim());
                        }
                        args.add(vals.toString());
                    }
                }
            }
            if (dbTable != null) {
                if (!hasTableInSql) {
                    if (quoteTableName) {
                        sql.append("\"");
                    }
                    sql.append(dbTable);
                    if (quoteTableName) {
                        sql.append("\"");
                    }
                }
                return true;
            } else {
                return false;
            }
        }

        public void doLoadRow(String classTag, Map<String, String> knownParams, Element tableNode, long pid) {

            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ");
            ;            StringBuilder cols = new StringBuilder();
            cols.append("PID");
            StringBuilder placeHolders = new StringBuilder();

            placeHolders.append("?");
            List<Object> args = new ArrayList<Object>();
            args.add(pid);

            for (Iterator<String> it = knownParams.keySet().iterator(); it.hasNext(); ) {
                String key = it.next();
                String val = knownParams.get(key);

                if (cols.length() > 0) {
                    cols.append(",");
                }
                cols.append(key);

                if (placeHolders.length() > 0) {
                    placeHolders.append(",");
                }
                placeHolders.append("?");

                args.add(val);
            }

            boolean foundDBTable = buildSQL(classTag, tableNode, sql, cols, placeHolders, args, false);


            LOG.debug("sql: {}", sql);
            if (foundDBTable) {

                sql.append("(").append(cols.toString()).append(") ");

                LOG.debug("sql: {}", sql, args);

                for (int i = 0; i < args.size(); i++) {
                    LOG.trace("idx: {}, arg: {}", i, args.get(i));
                }
                dshMapper.insertBomSiteAffectedItems(sql, args);
                catMapper.insertBomSiteAffectedItems(sql, args);

            }
        }

        public void doLoadRow(String classTag, String knownCol, String knownVal, Element tableNode, long pid) {
            Map<String, String> knownParams = new HashMap<String, String>();
            knownParams.put(knownCol, knownVal);
            doLoadRow(classTag, knownParams, tableNode, pid);
        }

        /**
         * @param namespaces xml命名空间
         * @param doc        xml文档对象
         * @param objectNode xml节点对象
         * @param finished   存储成功的元素集合
         */
        public void doJDBC(Map<String, String> namespaces, Document doc, Element objectNode, List<String> finished, long pid, String atoNumber, String currentFileName) {
            //agile class type name
            String classTag = objectNode.getName();
            //从元数据缓存中取该类型
            Map cfgByClass = (Map) metaCache.get(classTag);
            if (cfgByClass == null) {
                LOG.info("{} isn't configured to load through ETL.", classTag);
                return;
            }
            Set allowTabs = cfgByClass.keySet();
            List tableNodes = objectNode.elements();

            /**
             * 解析ITEM,BOM,AFFECTEDITEMS,SITES
             *
             */
            LOG.info("Start parsing sites and bom...... ");
            XMLLoader xmlLoader = new ItemXMLLoader();

            xmlLoader.processChildren(namespaces, objectNode, pid);

            LOG.info("Start parsing affectedItems and items......");

            XMLLoader xmlLoader1 = new ChangeXMLLoader();

            xmlLoader1.processChildren(namespaces, objectNode, pid);

            //handle P1/P2/P3
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ");

            StringBuilder cols = new StringBuilder();
            cols.append("PID,").append("ATO_NUMBER,").append("FILE_NUMBER,").append("CLASS");

            StringBuilder placeHolders = new StringBuilder();
            placeHolders.append("?,?,?,").append("?");

            List<Object> args = new ArrayList<Object>();
            args.add(pid);
            args.add(atoNumber);
            args.add(currentFileName);
            args.add(classTag);

            boolean hasTableNameInSql = false;
            for (int i = 0; i < tableNodes.size(); i++) {
                Element tableNode = (Element) tableNodes.get(i);

                String tableTag = tableNode.getName();

                if (!allowTabs.contains(tableTag)) {
                    continue;
                }

                if (!isTitleBlock(tableTag)) {
                    continue;
                }

                boolean foundTableName = buildSQL(classTag, tableNode, sql, cols, placeHolders, args, hasTableNameInSql);
                if (!hasTableNameInSql) {
                    hasTableNameInSql = foundTableName;
                }
            }

            if (hasTableNameInSql) {
                String number = getNumber(namespaces, objectNode);

                archive(getTableName(), getNumberCol(), number);


                sql.append("(").append(cols.toString()).append(")");


                LOG.debug("sql: {}", sql, args);

                for (int i = 0; i < args.size(); i++) {
                    //todo
                    LOG.debug("idx: {}, arg: {}", i, args.get(i));
                }


                //解析changerorder
                LOG.info("Start parsing changeOrder......");
                dshMapper.insertChangerOrders(sql, args);
                catMapper.insertChangerOrders(sql, args);


                if (!StringUtils.isEmpty(number)) {
                    finished.add(number);
                }
            }
        }

        public String getNumber(Map<String, String> namespaces, Element objectNode) {
            String number = selectSingleNodeText(objectNode, getXPathForNumber(), namespaces);

            LOG.debug("Number: {}", number);

            return number;
        }

        public void processChildren(Map<String, String> namespaces, Element objectNode, long pid) {
            //by default, it doesn't do anything
        }

        public void archive(String tableName, String numberCol, String number) {

            String sql = "UPDATE " + tableName + " SET SYNC_STATE = 1 WHERE " + numberCol + " = " + number;
            //dshMapper.updateState(sql,number);
            //catMapper.updateState(sql,number);
        }

        abstract String getTableName();

        abstract String getNumberCol();
    }

    public class FallbackXMLLoader extends XMLLoader {

        @Override
        String getNumberCol() {
            throw new UnsupportedOperationException();
        }

        @Override
        String getTableName() {
            throw new UnsupportedOperationException();
        }

        @Override
        public boolean supports(String nodeName) {
            throw new UnsupportedOperationException();
        }

        @Override
        public void doJDBC(Map<String, String> namespaces, Document doc, Element objectNode, List<String> finished, long pid, String atoNumber, String currentFileName) {
            LOG.warn("FallbackXMLLoader is invoked for {}", objectNode);
        }

        @Override
        public String getXPathForNumber() {
            throw new UnsupportedOperationException();
        }
    }

    public class ItemXMLLoader extends XMLLoader {

        @Override
        String getNumberCol() {
            return "ITEM_NUMBER";
        }

        @Override
        String getTableName() {
            return "SOURCE_ITEM";
        }

        @Override
        public String getXPathForNumber() {
            return "axml:TitleBlock/axml:Number";
        }

        @Override
        public boolean isTitleBlock(String tableName) {
            if ("TitleBlock".equalsIgnoreCase(tableName)) {
                return true;
            }

            return super.isTitleBlock(tableName);
        }

        @Override
        public boolean supports(String nodeName) {
            return "Parts".equalsIgnoreCase(nodeName) || "Documents".equalsIgnoreCase(nodeName);
        }

        @Override
        public void processChildren(Map<String, String> namespaces, Element objectNode, long pid) {
            String uniqueId = objectNode.attributeValue("uniqueId");
            String classTag = objectNode.getName();
            String itemNum = getNumber(namespaces, objectNode);

            archive("SOURCE_ITEM_SITE", getNumberCol(), itemNum);


            List sites = selectNodes(objectNode, xpathFilterSites, namespaces);

            if (sites != null) {
                for (int i = 0; i < sites.size(); i++) {
                    Element site = (Element) sites.get(i);
                    doLoadRow(classTag, "ITEM_NUMBER", itemNum, site, pid);
                }
            }
            archive("SOURCE_BOM", getNumberCol(), itemNum);


            String rev = selectSingleNodeText(objectNode, "axml:TitleBlock/axml:Rev", namespaces);
            Map<String, String> params = new HashMap<String, String>();

            params.put("ITEM_NUMBER", itemNum);
            List boms = selectNodes(objectNode, xpathFilterBOM, namespaces);

            if (boms != null) {
                for (int i = 0; i < boms.size(); i++) {
                    Element bom = (Element) boms.get(i);
                    doLoadRow(classTag, params, bom, pid);
                }
            }
        }
    }

    public class ChangeXMLLoader extends XMLLoader {

        @Override
        String getNumberCol() {
            return "CHANGE_NUMBER";
        }

        @Override
        String getTableName() {
            return "SOURCE_CHANGE";
        }

        @Override
        public String getXPathForNumber() {
            return "axml:CoverPage/axml:Number";
        }

        @Override
        public boolean isTitleBlock(String tableName) {
            if ("CoverPage".equalsIgnoreCase(tableName)) {
                return true;
            }

            return super.isTitleBlock(tableName);
        }

        @Override
        public boolean supports(String nodeName) {
            return "ChangeOrders".equalsIgnoreCase(nodeName)
                    || "ManufacturerOrders".equalsIgnoreCase(nodeName)
                    || "SiteChangeOrders".equalsIgnoreCase(nodeName);
        }

        @Override
        public void processChildren(Map<String, String> namespaces, Element objectNode, long pid) {
            String selector = xpathFilterAI;

            List nodes = selectNodes(objectNode, selector, namespaces);

            if (nodes != null) {
                String classTag = objectNode.getName();

                LOG.debug(classTag);

                String changeNum = getNumber(namespaces, objectNode);

                archive("SOURCE_AFFECTED_ITEMS", getNumberCol(), changeNum);

                for (int i = 0; i < nodes.size(); i++) {
                    Element node = (Element) nodes.get(i);
                    doLoadRow(classTag, "CHANGE_NUMBER", changeNum, node, pid);
                }
            }
        }
    }

    public class RowCallbackHandlerImpl implements RowCallbackHandler {

        public Map cache;

        public RowCallbackHandlerImpl(Map cache) {
            this.cache = cache;
        }

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            String classTag = rs.getString("CLASS_TAG");
            LOG.trace("classTag: {}", classTag);

            Map cfgByClass = (Map) cache.get(classTag);
            LOG.trace("cfgByClass: {}", cfgByClass);

            if (cfgByClass == null) {
                cfgByClass = new HashMap();
                cache.put(classTag, cfgByClass);
            }

            String tableTag = rs.getString("TABLE_TAG");
            LOG.trace("tableTag: {}", tableTag);

            Map cfgByTable = (Map) cfgByClass.get(tableTag);
            LOG.trace("cfgByTable: {}", cfgByTable);

            if (cfgByTable == null) {
                cfgByTable = new HashMap();
                cfgByClass.put(tableTag, cfgByTable);
            }

            String fieldTag = rs.getString("NODE_TAG");
            LOG.trace("fieldTag: {}", fieldTag);

            String[] cfgByField = (String[]) cfgByTable.get(fieldTag);
            LOG.trace("cfgByField: {}", Arrays.toString(cfgByField));

            if (cfgByField == null) {
                cfgByField = new String[2];
                cfgByField[0] = rs.getString("DB_TABLE");
                cfgByField[1] = rs.getString("DB_COL");

                cfgByTable.put(fieldTag, cfgByField);
            }
            LOG.trace("cfgByField: {}", Arrays.toString(cfgByField));
        }

    }
}
